SET SERVEROUTPUT ON

--Creating the procedure for calculating the free space for the USERS Tablespace.
CREATE OR REPLACE PROCEDURE users_free_space IS

tablespacename free_space_data.tablespace_name%type;
freespace free_space_data.free_space%type;
time free_space_data.timestamp%type;

--Creating the Cursor for retrieving multiple details from dba_free_space.
CURSOR space IS
	SELECT tablespace_name ,sum(bytes)/1024/1024 "MB Free"
	FROM dba_free_space
	WHERE tablespace_name = 'USERS'
	GROUP BY tablespace_name; 

BEGIN
OPEN space;
	LOOP
		--Fetching the details of the Cursor into the variables.
		FETCH space INTO tablespacename,freespace;
		EXIT WHEN space%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(tablespacename||freespace);
			
			--Inserting the data into the table free_space_data
			INSERT INTO free_space_data
						(tablespace_name,
						  free_space,
						  timestamp)
					VALUES
						(tablespacename,
						 freespace,
						 sysdate);
		
	END LOOP;
CLOSE space;
	
END users_free_space;
/
--Used in order to just confirm.
